using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using SaleOffVn.BusinessEntities;

namespace SaleOffVn.DataAccess
{
    /// <summary>
    /// Represents a picture
    /// </summary>
    public partial class DBPicture
    {
        #region Private methods
            private static Picture FillDataRecord(IDataRecord myDataRecord)
            {
                Picture pic = new Picture();

                pic.PictureId = myDataRecord.GetInt32(myDataRecord.GetOrdinal("PictureID"));
                pic.PictureThumbnail = myDataRecord.IsDBNull(myDataRecord.GetOrdinal("PictureThumbnail")) ? "" : myDataRecord.GetString(myDataRecord.GetOrdinal("PictureThumbnail"));
                return pic;
            }
        #endregion Private methods

        public static Picture GetPictureById(int imageID)
        {
            Picture pic = null;            
            using (SqlConnection myConnection = new SqlConnection(AppConfiguration.ConnectionString))
            {
                using (SqlCommand myCommand = new SqlCommand("usp_InfoNam_PicGetInfo", myConnection))
                {
                    myCommand.CommandType = CommandType.StoredProcedure;
                    myCommand.Parameters.AddWithValue("@PictureID", imageID);
                    myConnection.Open();
                    using (SqlDataReader myReader = myCommand.ExecuteReader())
                    {
                        if (myReader.Read())
                        {
                            pic = FillDataRecord(myReader);
                        }
                        myReader.Close();
                    }
                }
                myConnection.Close();
            }
            return pic;
        }
        
        public static DataTable GetAllPictures()
        {
            DataTable dt = new DataTable();
            string strSQL = "SELECT * FROM dbo.InfoNam_Picture ORDER BY PictureID DESC";
            SqlConnection cn = new SqlConnection(AppConfiguration.ConnectionString);
            SqlCommand cmd = new SqlCommand(strSQL, cn);
            SqlDataAdapter dAdapter;
            cmd.CommandTimeout = 300;
            dAdapter = new SqlDataAdapter(cmd);
            dAdapter.Fill(dt);
            cn.Close();
            return dt;
        }

        public static int GetMaximumPictureID()
        {
            int iReturn = 0;
            string strSQL = "SELECT max(PictureID) as CurrentID FROM dbo.InfoNam_Picture";
            SqlConnection myConnection = new SqlConnection(AppConfiguration.ConnectionString);
            SqlCommand myCommand = new SqlCommand();
            myConnection.Open();
            myCommand.CommandText = strSQL;
            myCommand.CommandTimeout = 300;
            myCommand.Connection = myConnection;
            string strResult = myCommand.ExecuteScalar().ToString();
            if (strResult == String.Empty)
                iReturn = 0;
            else
                iReturn = Int32.Parse(strResult.ToString());
            if (iReturn < 0)
            {
                throw new DBConcurrencyException("The PictureID cannot be got");
            }
            myConnection.Close();
            return iReturn;
        }
        
        public static int Add(string PictureThumbnail)
        {
            int iPicID = 0;
            using (SqlConnection myConnection = new SqlConnection(AppConfiguration.ConnectionString))
            {
                using (SqlCommand myCommand = new SqlCommand("usp_InfoNam_PicAdd", myConnection))
                {
                    myCommand.CommandType = CommandType.StoredProcedure;

                    myCommand.Parameters.AddWithValue("@PictureThumbnail", PictureThumbnail);
                    myConnection.Open();
                    iPicID = Int32.Parse(myCommand.ExecuteScalar().ToString());
                    if (iPicID <= 0)
                    {
                        throw new DBConcurrencyException("The Picture cannot be added");
                    }
                }
                myConnection.Close();
            }
            return iPicID;
        }

        /// <summary>Delete a picture from database.</summary>
        /// <param name="accountId">The ID of the picture to delete.</param>
        /// <returns></returns>
        public static void Delete(int pictureID)
        {
            using (SqlConnection myConnection = new SqlConnection(AppConfiguration.ConnectionString))
            {
                using (SqlCommand myCommand = new SqlCommand("usp_InfoNam_PicDelete", myConnection))
                {
                    myCommand.CommandType = CommandType.StoredProcedure;
                    myCommand.Parameters.AddWithValue("@PictureID", pictureID);
                    myConnection.Open();
                    myCommand.ExecuteNonQuery();
                }
                myConnection.Close();
            }
        }
    }
}
